ROLLBACK TO SAVEPOINT
ROLLBACK TO SAVEPOINT — Roll back to a savepoint
Synopsis
ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] savepoint_name
Description
Roll back all commands executed after the savepoint was established. The savepoint remains valid and can be rolled back to again later (if needed).
ROLLBACK TO SAVEPOINT implicitly destroys all savepoints established after the named savepoint.
Parameters
savepoint_name
The savepoint to roll back to.
Notes
Use RELEASE SAVEPOINT to destroy a savepoint without discarding the effects of commands executed after it was established.
Specifying a savepoint that has not been established is an error.
Cursors have somewhat non-transactional behavior with respect to savepoints. When a savepoint is rolled back, any cursors opened within that savepoint will be closed. If a previously opened cursor is affected by a FETCH or MOVE command within a savepoint, and that savepoint is later rolled back, the cursor will remain at the position that FETCH moved it to (i.e., the cursor movement caused by FETCH is not rolled back). Rolling back cannot undo closing a cursor either. However, other side effects caused by the cursor query (such as side effects of volatile functions called by the query) can be rolled back, as long as they occurred during a savepoint that was later rolled back. If a cursor's execution causes the transaction to abort, it is placed in a state where it cannot be executed, so the cursor will no longer be usable after the transaction is restored with ROLLBACK TO SAVEPOINT.
Examples
-- To undo the effects of commands executed after my_savepoint was established:
ROLLBACK TO SAVEPOINT my_savepoint;
-- Cursor positions are not affected by savepoint rollbacks:
BEGIN;
DECLARE foo CURSOR FOR SELECT 1 UNION SELECT 2;
SAVEPOINT foo;
FETCH 1 FROM foo;
?column?
ROLLBACK TO SAVEPOINT
----------
1
ROLLBACK TO SAVEPOINT foo;
FETCH 1 FROM foo;
?column?
----------
2
COMMIT;